﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using FOModels.AccumulateDeposit;
namespace DataAccess.AccumulateDeposit
{
    public class AccumulateDepositDAL
    {
        private SQLDataAccess db;
        public AccumulateDepositDAL()
        {
            db = new SQLDataAccess();
        }
        public DataTable getTOP1()
        {
            return db.exeSelect("Select top 1 * from AccumulateDeposit order by AccumulateDepositID desc");
        }
        public DataTable getAccumulatedSavingDefaultByProvider(int CompanyID, int langid)
        {
            return db.exeSelect("Select sa.AccumulateDepositID,al.AccumulateDepositName,al.Benefit,sa.CompanyID,sa.InterestRate,sa.MinTermDepositBalance,sa.MaxTermDepositBalance,al.[Description],al.FeeContent,al.MoreInfo,sa.Display,cl.Name,co.CompanyLogo from AccumulateDeposit sa join AccumulateDepositLang al on sa.AccumulateDepositID = al.AccumulateDepositID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and sa.MinTermDepositBalance <= 1000000 and sa.MaxTermDepositBalance >= 1000000 and Display = 1 and cl.LangID = " + langid + " and al.LangID = " + langid + " and sa.CompanyID = " + CompanyID + "");
        }
        public DataTable getAccumulateDepositDefalt(int langid)
        {
            return db.exeSelect("Select sa.AccumulateDepositID,al.AccumulateDepositName,al.Benefit,sa.CompanyID,sa.InterestRate,sa.MinTermDepositBalance,sa.MaxTermDepositBalance,al.[Description],al.FeeContent,al.MoreInfo,sa.Display,cl.Name,co.CompanyLogo from AccumulateDeposit sa join AccumulateDepositLang al on sa.AccumulateDepositID = al.AccumulateDepositID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and sa.MinTermDepositBalance <= 1000000 and sa.MaxTermDepositBalance >= 1000000 and Display = 1 and cl.LangID = " + langid + " and al.LangID = " + langid + "");
        }
        public DataTable getAccumulateDepositList(string termbalance, int langid)
        {
            return db.exeSelect("Select sa.AccumulateDepositID,al.AccumulateDepositName,al.Benefit,sa.CompanyID,sa.InterestRate,sa.MinTermDepositBalance,sa.MaxTermDepositBalance,al.[Description],al.FeeContent,al.MoreInfo,sa.Display,cl.Name,co.CompanyLogo from AccumulateDeposit sa join AccumulateDepositLang al on sa.AccumulateDepositID = al.AccumulateDepositID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and cl.LangID = " + langid + " and al.LangID = " + langid + " and sa.MinTermDepositBalance <= " + termbalance + " and sa.MaxTermDepositBalance >= " + termbalance + " and Display =1 ");
        }
        public DataTable getAccumulateDepositListByProvider(string termbalance, int CompanyID, int langid)
        {
            return db.exeSelect("Select sa.AccumulateDepositID,al.AccumulateDepositName,al.Benefit,sa.CompanyID,sa.InterestRate,sa.MinTermDepositBalance,sa.MaxTermDepositBalance,al.[Description],al.FeeContent,al.MoreInfo,sa.Display,cl.Name,co.CompanyLogo from AccumulateDeposit sa join AccumulateDepositLang al on sa.AccumulateDepositID = al.AccumulateDepositID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and cl.LangID = " + langid + " and al.LangID = " + langid + " and sa.MinTermDepositBalance <= " + termbalance + " and sa.MaxTermDepositBalance >= " + termbalance + " and Display = 1 and sa.CompanyID = " + CompanyID + "");
        }
        public DataTable getAccumulateDepositByID(int AccumulateDepositID, int langid)
        {
            return db.exeSelect("Select sa.AccumulateDepositID,al.AccumulateDepositName,al.Benefit,sa.CompanyID,sa.InterestRate,sa.MinTermDepositBalance,sa.MaxTermDepositBalance,al.[Description],al.FeeContent,al.MoreInfo,sa.Display,cl.Name,co.CompanyLogo from AccumulateDeposit sa join AccumulateDepositLang al on sa.AccumulateDepositID = al.AccumulateDepositID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and cl.LangID = " + langid + " and al.LangID= " + langid + " and sa.AccumulateDepositID = " + AccumulateDepositID + "");
        }
        public DataTable getAccumulateDepositByIDManager(int AccumulateDepositID, int langid)
        {
            return db.exeSelect("Select sa.AccumulateDepositID,al.AccumulateDepositName,al.Benefit,sa.CompanyID,sa.InterestRate,sa.MinTermDepositBalance,sa.MaxTermDepositBalance,al.[Description],al.FeeContent,al.MoreInfo,sa.Display,cl.Name,co.CompanyLogo from AccumulateDeposit sa join AccumulateDepositLang al on sa.AccumulateDepositID = al.AccumulateDepositID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where cl.LangID = " + langid + " and al.LangID= " + langid + " and sa.AccumulateDepositID = " + AccumulateDepositID + "");
        }
        public DataTable getaccumulatedsavingrelati(int CompanyID, int AccumulateDepositID, int langid)
        {
            return db.exeSelect("Select sa.AccumulateDepositID,al.AccumulateDepositName,al.Benefit,sa.CompanyID,sa.InterestRate,sa.MinTermDepositBalance,sa.MaxTermDepositBalance,al.[Description],al.FeeContent,al.MoreInfo,sa.Display,cl.Name,co.CompanyLogo from AccumulateDeposit sa join AccumulateDepositLang al on sa.AccumulateDepositID = al.AccumulateDepositID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and cl.LangID = " + langid + " and al.LangID = " + langid + " and Display = 1  and ca.CompanyID =" + CompanyID + " and ca.AccumulateDepositID !=" + AccumulateDepositID + "");
        }
        public DataTable getall(int langid)
        {
            return db.exeSelect("Select sa.AccumulateDepositID,al.AccumulateDepositName,al.Benefit,sa.CompanyID,sa.InterestRate,sa.MinTermDepositBalance,sa.MaxTermDepositBalance,al.[Description],al.FeeContent,al.MoreInfo,sa.Display,cl.Name,co.CompanyLogo,la.FlagImage from AccumulateDeposit sa join AccumulateDepositLang al on sa.AccumulateDepositID = al.AccumulateDepositID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join Language la on al.LangID = la.ID where cl.LangID = " + langid + " and al.LangID = " + langid + "");
        }
        public int UpdateAccumulateDeposit(AccumulateDepositModels models)
        {
            return db.exeUpdate("update AccumulateDeposit set AccumulateDepositName = N'" + models.AccumulateDepositName + "',MinTermDepositBalance=" + double.Parse(models.MinTermDepositBalance) + ",MaxTermDepositBalance=" + double.Parse(models.MaxTermDepositBalance) + ",InterestRate=" + models.InterestRate + ",CompanyID= " + models.CompanyID + ",Display='" + models.Display + "' where AccumulateDepositID = " + models.AccumulateDepositID + "");
        }
        public int InsertAccumulateDepositLang(AccumulateDepositModels models, int langid)
        {
            return db.exeUpdate("insert into AccumulateDepositLang values (" + models.AccumulateDepositID + "," + langid + ",N'" + models.AccumulateDepositName + "',N'" + models.Benefit + "',N'" + models.Description + "',N'" + models.FeeContent + "',N'" + models.MoreInfo + "')");
        }
        public int DeleteAccumulateDeposit(int AccumulateDepositID)
        {
            return db.exeUpdate("Delete from AccumulateDeposit where AccumulateDepositID = " + AccumulateDepositID + "");
        }
        public int UpdateAccumulateDepositLang(AccumulateDepositModels models, int langid)
        {
            return db.exeUpdate("update AccumulateDepositLang set AccumulateDepositName = N'" + models.AccumulateDepositName + "',Description=N'" + models.Description + "',Benefit=N'" + models.Benefit + "',FeeContent=N'" + models.FeeContent + "',MoreInfo=N'" + models.MoreInfo + "' where AccumulateDepositID = " + models.AccumulateDepositID + " and LangID=" + langid + "");
        }
        public int InsertAccumulateDeposit(AccumulateDepositModels models)
        {
            return db.exeUpdate("insert into AccumulateDeposit values (N'" + models.AccumulateDepositName + "'," + models.CompanyID + "," + models.InterestRate + "," + double.Parse(models.MinTermDepositBalance) + "," + double.Parse(models.MaxTermDepositBalance) + ",'" + models.Display + "')");
        }
        public int DeleteAccumulateDepositLang(int AccumulateDepositID)
        {
            return db.exeUpdate("Delete from AccumulateDepositLang where AccumulateDepositID = " + AccumulateDepositID + "");
        }

        public int DeleteAccumulateDepositApply(int AccumulateDepositApplyID)
        {
            return db.exeUpdate("Delete from AccumulateDepositApply where AccumulateDepositApplyID = " + AccumulateDepositApplyID + "");
        }
        public int UpdateStatus(AccumulateDepositApplyModels card)
        {
            return db.exeUpdate("update AccumulateDepositApply set StatusID = " + card.StatusID + " where AccumulateDepositApplyID = " + card.AccumulateDepositApplyID + "");
        }
        public int AccumulateDepositApply(int AccumulateDepositID, AccumulateDepositApplyModels app)
        {
            return db.exeUpdate("insert into AccumulateDepositApply(AccumulateDepositID,CustomerName,CustomerEmail,CustomerPhone,CustomerIncome,CustomerDOB,StatusID) values (" + AccumulateDepositID + ",N'" + app.CustomerName + "',N'" + app.CustomerEmail + "',N'" + app.CustomerPhone + "',N'" + app.CustomerIncome + "',N'" + app.CustomerDOB + "'," + app.StatusID + ")");
        }
        public DataTable getcontractbyid(int cardapplyid)
        {
            return db.exeSelect("select * from AccumulateDepositApply ca join AccumulateDeposit c on ca.AccumulateDepositID = c.AccumulateDepositID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.AccumulateDepositApplyID = " + cardapplyid + " and cl.LangID = 1");
        }
        public DataTable getcontract()
        {
            return db.exeSelect("select * from AccumulateDepositApply ca join [AccumulateDeposit] c on ca.AccumulateDepositID = c.AccumulateDepositID join [Status] ta on ca.StatusID  = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and cl.LangID = 1");
        }
        public DataTable getcontractbystatus(int statusid)
        {
            return db.exeSelect("select * from AccumulateDepositApply ca join [AccumulateDeposit] c on ca.AccumulateDepositID = c.AccumulateDepositID join [Status] ta on ca.StatusID  = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and cl.LangID = 1 and ca.StatusID = " + statusid + "");
        }
    }
}
